package vn.edu.saigontech.ncms.service.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.Teacher;
import vn.edu.saigontech.ncms.model.teaching.TeachingInterruption;
import vn.edu.saigontech.ncms.model.teaching.TeachingSwitch;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.TeacherService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;

@SuppressWarnings("unchecked")
public class OfferingServiceImpl extends
		HibernateGenericService<Offering, Long> implements OfferingService {

	@Override
	public Collection<Offering> find(Offering offering) {
		Criteria criteria = this.createCriteria();
		if (!Checker.isEmpty(offering.getName())) {
			criteria.add(Restrictions.ilike("name", offering.getName(),
					MatchMode.ANYWHERE));
		}
		if (!Checker.isNull(offering.getLevel())) {
			criteria.createCriteria("level").add(
					Restrictions.eq("id", offering.getLevel().getId()));
		}
		if (!Checker.isNull(offering.getStartDate())) {
			criteria.add(Restrictions.eq("startDate", offering.getStartDate()));
		}
		if (!Checker.isNull(offering.getEndDate())) {
			criteria.add(Restrictions.eq("endDate", offering.getEndDate()));
		}
		return criteria.list();
	}
	
	public Collection<Offering> findByProgram(Long programId) {
		Criteria offeringCriteria = this.createCriteria();
		Criteria levelCriteria = offeringCriteria.createCriteria("level");
		if(!Checker.isNull(programId)) {
			levelCriteria.createCriteria("program").add(Restrictions.eq("id", programId));
		}
		offeringCriteria.addOrder(Order.desc("endDate"));
		return offeringCriteria.list();
	}

	public Collection<Offering> find(String name, Long programId, Long levelId, Long roomId,
			Long shiftId, Date startDate, Date endDate, Date fromDate, Date toDate,
			Date midtermWrittenDate, Date finalSpokenDate,
			Date finalWrittenDate, Date issueDate, Date cambridgeSpokenDate, Date cambridgeWrittenDate,
			Long monTeacherId, Long tueTeacherId, Long wedTeacherId, Long thuTeacherId,
			Long friTeacherId, Long satTeacherId, Long sunTeacherId, String comment) {
		Criteria offeringCriteria = this.createCriteria();
		Criteria levelCriteria = offeringCriteria.createCriteria("level");
		if (!Checker.isEmpty(name)) {
			offeringCriteria.add(Restrictions.ilike("name", name,
					MatchMode.ANYWHERE));
		}
		if(!Checker.isNull(programId)) {
			levelCriteria.createCriteria("program").add(Restrictions.eq("id", programId));
		}
		if (!Checker.isNull(levelId)) {
			levelCriteria.add(Restrictions.eq("id", levelId));
		}
		if (!Checker.isNull(roomId)) {
			offeringCriteria.createCriteria("room").add(
					Restrictions.eq("id", roomId));
		}
		if (!Checker.isNull(shiftId)) {
			offeringCriteria.createCriteria("shift").add(
					Restrictions.eq("id", shiftId));
		}
		if (!Checker.isNull(startDate)) {
			offeringCriteria.add(Restrictions.eq("startDate", startDate));
		}
		if (!Checker.isNull(endDate)) {
			offeringCriteria.add(Restrictions.eq("endDate", endDate));
		}
		if(Checker.isNotNull(fromDate)) {
			offeringCriteria.add(Restrictions.ge("startDate", fromDate));
		}
		if(Checker.isNotNull(toDate)) {
			offeringCriteria.add(Restrictions.le("startDate", toDate));
		}
//		if(Checker.isNotNull(fromDate) && Checker.isNotNull(toDate)) {
//			offeringCriteria.add(Restrictions.between("startDate", fromDate, toDate));
//		}
		if (!Checker.isNull(midtermWrittenDate)) {
			offeringCriteria.add(Restrictions.eq("midtermWrittenDate",
					midtermWrittenDate));
		}
		if (!Checker.isNull(finalSpokenDate)) {
			offeringCriteria.add(Restrictions.eq("finalSpokenDate",
					finalSpokenDate));
		}
		if (!Checker.isNull(finalWrittenDate)) {
			offeringCriteria.add(Restrictions.eq("finalWrittenDate",
					finalWrittenDate));
		}
		if (!Checker.isNull(issueDate)) {
			offeringCriteria.add(Restrictions.eq("issueDate", issueDate));
		}
		if (!Checker.isNull(cambridgeSpokenDate)) {
			offeringCriteria.add(Restrictions.eq("cambridgeSpokenDate",
					cambridgeSpokenDate));
		}
		if (!Checker.isNull(cambridgeWrittenDate)) {
			offeringCriteria.add(Restrictions.eq("cambridgeWrittenDate",
					cambridgeWrittenDate));
		}
		if (!Checker.isNull(monTeacherId)) {
			offeringCriteria.createCriteria("monTeacher").add(
					Restrictions.eq("id", monTeacherId));
		}
		if (!Checker.isNull(tueTeacherId)) {
			offeringCriteria.createCriteria("tueTeacher").add(
					Restrictions.eq("id", tueTeacherId));
		}
		if (!Checker.isNull(wedTeacherId)) {
			offeringCriteria.createCriteria("wedTeacher").add(
					Restrictions.eq("id", wedTeacherId));
		}
		if (!Checker.isNull(thuTeacherId)) {
			offeringCriteria.createCriteria("thuTeacher").add(
					Restrictions.eq("id", thuTeacherId));
		}
		if (!Checker.isNull(friTeacherId)) {
			offeringCriteria.createCriteria("friTeacher").add(
					Restrictions.eq("id", friTeacherId));
		}
		if (!Checker.isNull(satTeacherId)) {
			offeringCriteria.createCriteria("satTeacher").add(
					Restrictions.eq("id", satTeacherId));
		}
		if (!Checker.isNull(sunTeacherId)) {
			offeringCriteria.createCriteria("sunTeacher").add(
					Restrictions.eq("id", sunTeacherId));
		}
		if (!Checker.isEmpty(comment)) {
			offeringCriteria.add(Restrictions.ilike("comment", comment,
					MatchMode.ANYWHERE));
		}
		offeringCriteria.addOrder(Order.desc("endDate"));
		return offeringCriteria.list();
	}

	@Override
	public Collection<Offering> findAll() {
		Criteria offeringCriteria = this.createCriteria();
		Criteria levelCriteria = offeringCriteria.createCriteria("level");
		levelCriteria.addOrder(Order.desc("id"));
		return offeringCriteria.list();
	}
	
	public Collection<Offering> findAllSortByStartDate() {
		Criteria offeringCriteria = this.createCriteria();
		offeringCriteria.addOrder(Order.desc("startDate"));
		return offeringCriteria.list();
	}

	public Collection<Offering> findByNameStartWith(String prefix) {
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.ilike("name", prefix, MatchMode.START));
		criteria.addOrder(Order.asc("name")).addOrder(Order.desc("startDate"));
		return criteria.list();
	}

	public Collection<Offering> findByMonthAndYear(int month, int year) {
		Collection<Offering> result = new ArrayList<Offering>();
		Collection<Offering> offerings = this.findAllSortedByLevelOrder();
		for (Offering offering : offerings) {
			int startMonth = DateUtil.getMonth(offering.getStartDate());
			int startYear = DateUtil.getYear(offering.getStartDate());
			int endMonth = DateUtil.getMonth(offering.getEndDate());
			int endYear = DateUtil.getYear(offering.getEndDate());
			if ((startYear == endYear && endYear == year)
					&& ((month == startMonth) || (month == endMonth) || (month >= startMonth && month <= endMonth))) {
				result.add(offering);
			}
		}
		return result;
	}

	@Override
	public Collection<Offering> findByDate(Date from, Date to) {
		Criteria criteria = this.createCriteria();
		criteria.addOrder(Order.asc("startDate"));
		criteria.createCriteria("level").addOrder(Order.asc("name"));
		Collection<Offering> offerings = criteria.list();

		Collection<Offering> result = new ArrayList<Offering>();
		Collection<Date> dates1 = DateUtil.sequence(from, to);
		for (Offering each : offerings) {
			Collection<Date> dates0 = DateUtil.sequence(each.getStartDate(), each.getEndDate());
			dates0.retainAll(dates1);
			if (!dates0.isEmpty()) {
				result.add(each);
			}
		}
		return result;
	}
	
	@Override
	public Collection<Offering> findByMidTestAndDate(Date from, Date to) {
		String startDate = DateUtil.toString(from, "MM/dd/yyyy");
		String endDate = DateUtil.toString(to, "MM/dd/yyyy");
				
		String queryString = "SELECT * FROM ncms_offering as o WHERE " +
			"o.midterm_written_date BETWEEN '"+ startDate +"' AND '"+ endDate +"' " +
			"AND o.cancel = false " +
			" ORDER BY o.name";
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Offering.class);
		return query.list();
	}
	
	public Collection<Offering> findByFinalTestAndDate(Date from, Date to) {
		String startDate = DateUtil.toString(from, "MM/dd/yyyy");
		String endDate = DateUtil.toString(to, "MM/dd/yyyy");
				
		String queryString = "SELECT * FROM ncms_offering as o WHERE " +
			"o.end_date BETWEEN '"+ startDate +"' AND '"+ endDate +"'" +
			" ORDER BY o.name";
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Offering.class);
		return query.list();
	}
	
	public Collection<Offering> findByTestDate(Date from, Date to) {
		String startDate = DateUtil.toString(from, "MM/dd/yyyy");
		String endDate = DateUtil.toString(to, "MM/dd/yyyy");
				
		String queryString = "SELECT * FROM ncms_offering as o WHERE " +
			"(o.end_date BETWEEN '"+ startDate +"' AND '"+ endDate +"' OR " +
			"o.final_written_date BETWEEN '"+ startDate +"' AND '"+ endDate +"' OR " +
			"o.final_spoken_date BETWEEN '"+ startDate +"' AND '"+ endDate +"') OR " +
			"o.midterm_written_date BETWEEN '"+ startDate +"' AND '"+ endDate +"'" +
			" ORDER BY o.name ASC";
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Offering.class);
		return query.list();
	}

	public Collection<Offering> findByTeacherId(Long teacherId) {
		Collection<Offering> result = new ArrayList<Offering>();
		Collection<Offering> offerings = this.findAll();
		Teacher teacher = TeacherService.INSTANCE.findById(teacherId);
		for (Offering offering : offerings) {
			if (offering.containsInstructor(teacher) && !result.contains(teacher)) {
				result.add(offering);
			}
		}
		return result;
	}
	
	public List<Offering> findByTeacherIdSortByStartDate(Long teacherId) {
		List<Offering> result = new ArrayList<Offering>();
		Collection<Offering> offerings = this.findAllSortByStartDate();
		Teacher teacher = TeacherService.INSTANCE.findById(teacherId);
		
		Collection<TeachingInterruption> interupts = TeachingInterruptionServiceImpl.INSTANCE.findBySubstituteTeacher(teacherId);
		Collection<TeachingSwitch> switches = TeachingSwitchServiceImpl.INSTANCE.findBySwitchTeacher(teacherId);
		
		loop:for (Offering offering : offerings) {
			if (offering.containsInstructor(teacher)) {
				result.add(offering);
				continue;
			}
			// TODO: review later
			for (TeachingInterruption interupt : interupts)
				if (interupt.getOffering().equals(offering) ) {
					result.add(offering);
					continue loop;
				}
			for (TeachingSwitch aSwitch : switches)
				if (aSwitch.getOffering().equals(offering)) {
					result.add(offering);
					continue loop;
				}
		}
		return result;
	}

	private Collection<Offering> findAllSortedByLevelOrder() {
		Criteria offeringCriteria = this.createCriteria();
		Criteria levelCriteria = offeringCriteria.createCriteria("level");
		levelCriteria.addOrder(Order.asc("order"));
		return offeringCriteria.list();
	}

	@Override
	public int countByRoomId(Long roomId) {
		Criteria criteria = this.createCriteria();
		criteria.createCriteria("room").add(Restrictions.eq("id", roomId));
		criteria.setProjection(Projections.rowCount());
		return (Integer) criteria.uniqueResult();
	}
	
	@Override
	public Offering findByNextOffering(Long nextOfferingId) {
		Criteria criteria = this.createCriteria();
		Criteria nextOfferingCriteria = criteria.createCriteria("nextOffering");
		nextOfferingCriteria.add(Restrictions.eq("id", nextOfferingId));
		return (Offering) criteria.uniqueResult();
	}

	@Override
	public Collection<Offering> findRecently() {
		Criteria offeringCriteria = this.createCriteria();
		offeringCriteria.addOrder(Order.desc("id"));
		offeringCriteria.setFirstResult(0);
		offeringCriteria.setMaxResults(5);
		return offeringCriteria.list();
	}

	@Override
	public Collection<Date> findTeachingDays(Long id) {
		Offering offering = super.findById(id);
		Set<Date> dates = new TreeSet<Date>();

		Set<Integer> days = offering.getDays();
		List<Date> listOfDates = DateUtil.sequenceAsList(offering.getStartDate(), offering.getEndDate());
		for (Date date : listOfDates) {
			int thu = DateUtil.toCalendar(date).get(Calendar.DAY_OF_WEEK);
			if (days.contains(thu)) {
				dates.add(date);
			}
		}
		return dates;
	}

	@Override
	public Collection<Date> findTeachingDays(Long id, Date from, Date to) {
		Collection<Date> result = new TreeSet<Date>();
		Collection<Date> teachingDays = this.findTeachingDays(id);

		for (Date each : DateUtil.sequence(from, to)) {
			if (teachingDays.contains(each)) {
				result.add(each);
			}
		}
		return result;
	}

	@Override
	public Collection<Offering> findByDateAndTeacher(Long teacherId, Date first, Date last) {
		Criteria criteria = this.createCriteria();
		criteria.addOrder(Order.asc("startDate"));
		criteria.createCriteria("level").addOrder(Order.asc("name"));
		Collection<Offering> offerings = criteria.list();

		Collection<Offering> result = new ArrayList<Offering>();
		Collection<Date> dates1 = DateUtil.sequence(first, last);
		Teacher teacher = TeacherService.INSTANCE.findById(teacherId);
		
		for (Offering each : offerings) {
			Collection<Date> dates0 = DateUtil.sequence(each.getStartDate(), each.getEndDate());
			dates0.retainAll(dates1);
			if (!dates0.isEmpty() && each.containsInstructorEven(teacher, first, last) 
					&& !result.contains(teacher) && !result.contains(each)) {
				result.add(each);
			}
		}
		return result;
	}

	@Override
	public Collection<Offering> findAllOpening() {
		Date currDate = DateUtil.newDate();
		int month = DateUtil.getMonth(currDate) + 1;
		int year = DateUtil.getYear(currDate);
				
		return findAllOpeningByMonthYear(month, year);
	}
	
	@Override
	public Collection<Offering> findAllOpeningByMonthYear(int month, int year) {
		String queryString = "SELECT * FROM ncms_offering as o " +
			"WHERE o.end_date > ? AND o.start_date < ? " +
			"AND o.cancel = FALSE";
		
		SQLQuery query = getSession().createSQLQuery(queryString);
		query.addEntity(Offering.class);
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		return query.setDate(0, startDate).setDate(1, endDate).list();
	}
}